import pymysql
from tkinter import messagebox, scrolledtext
import tkinter as tk
import logging
import pandas as pd
# 连接mysql数据库并导出查询sql内容的excel表格数据文件。
# 需要安装的库：pymysql, pandas, tkinter, logging
# 配置日志记录
logging.basicConfig(filename='app.log', level=logging.DEBUG, format='%(asctime)s - %(levelname)s - %(message)s')

class DatabaseQueryTool:
    def __init__(self, root):
        self.root = root
        self.root.title("数据库查询工具")
        self.root.geometry("500x500")

        # 创建输入框和标签
        self.create_input_fields()
        self.create_buttons()

    def create_input_fields(self):
        # 将 labels_and_entries 定义为实例属性
        self.labels_and_entries = [
            ("主机:", "host_entry"),
            ("用户名:", "user_entry"),
            ("密码:", "password_entry"),
            ("数据库:", "database_entry"),
            ("端口号:", "port_entry"),
            ("导出文件名:", "export_filename_entry"),
        ]

        for idx, (label_text, entry_name) in enumerate(self.labels_and_entries):
            tk.Label(self.root, text=label_text).grid(row=idx, column=0, padx=10, pady=5, sticky=tk.W)
            if entry_name == "password_entry":
                entry = tk.Entry(self.root, width=30, show="*")  # 隐藏密码字符
            else:
                entry = tk.Entry(self.root, width=30)
            setattr(self, entry_name, entry)
            getattr(self, entry_name).grid(row=idx, column=1, padx=10, pady=5)

        # 默认值设置
        self.port_entry.insert(0, "3306")  # 默认端口号
        self.export_filename_entry.insert(0, "output.xlsx")  # 默认文件名

        # 查询SQL输入框
        tk.Label(self.root, text="查询SQL:").grid(row=len(self.labels_and_entries), column=0, padx=10, pady=5, sticky=tk.W)
        self.query_text = scrolledtext.ScrolledText(self.root, width=50, height=10)
        self.query_text.grid(row=len(self.labels_and_entries), column=1, padx=10, pady=5)

    def create_buttons(self):
        # 固定行号，从输入框和查询SQL文本框之后开始
        start_row = len(self.labels_and_entries) + 1  # 输入框行数 + 查询SQL文本框行数

        buttons = [
            ("执行查询并导出Excel", self.execute_query),
            ("预览前1000条记录", self.preview_query),
            ("测试数据库连接", self.test_connect_to_database),
        ]

        for idx, (button_text, command) in enumerate(buttons, start=start_row):
            tk.Button(self.root, text=button_text, command=command).grid(row=idx, column=0, columnspan=2, pady=10)

    def connect_to_database(self):
        logging.debug("开始连接数据库")
        try:
            host = self.host_entry.get().strip()
            user = self.user_entry.get().strip()
            password = self.password_entry.get().strip()
            database = self.database_entry.get().strip()
            port_str = self.port_entry.get().strip()

            if not all([host, user, password, database]):
                raise ValueError("所有字段不能为空")

            if not port_str.isdigit():
                raise ValueError("端口号必须是数字")

            port = int(port_str)

            conn = pymysql.connect(
                host=host,
                user=user,
                password=password,  # 直接使用原始密码
                database=database,
                port=port,
                cursorclass=pymysql.cursors.DictCursor
            )
            logging.info("数据库连接成功")
            return conn
        except pymysql.MySQLError as db_err:
            logging.error(f"数据库连接失败: {db_err}", exc_info=True)
            messagebox.showerror("错误", f"数据库连接失败: {db_err}")
        except Exception as e:
            logging.error(f"连接失败: {e}", exc_info=True)
            messagebox.showerror("错误", f"连接失败: {e}")
        return None

    def execute_query(self):
        logging.debug("开始执行查询")
        conn = self.connect_to_database()
        if conn is None:
            return

        query = self.query_text.get("1.0", tk.END).strip()
        if not query:
            messagebox.showwarning("警告", "查询SQL不能为空")
            return

        try:
            with conn.cursor() as cursor:
                cursor.execute(query)
                result = cursor.fetchall()
                df = pd.DataFrame(result)
                export_filename = self.export_filename_entry.get().strip()
                df.to_excel(export_filename, index=False)
                messagebox.showinfo("成功", f"数据已成功导出到 {export_filename}")
        except pymysql.MySQLError as db_err:
            logging.error(f"查询执行失败: {db_err}", exc_info=True)
            messagebox.showerror("错误", f"查询执行失败: {db_err}")
        except Exception as e:
            logging.error(f"查询执行失败: {e}", exc_info=True)
            messagebox.showerror("错误", f"查询执行失败: {e}")
        finally:
            conn.close()

    def preview_query(self):
        logging.debug("开始预览查询")
        conn = self.connect_to_database()
        if conn is None:
            return

        query = self.query_text.get("1.0", tk.END).strip()
        if not query:
            messagebox.showwarning("警告", "查询SQL不能为空")
            return

        try:
            with conn.cursor() as cursor:
                cursor.execute(query)
                result = cursor.fetchmany(1000)
                df = pd.DataFrame(result)
                preview_window = tk.Toplevel(self.root)
                preview_window.title("查询结果预览")
                preview_text = scrolledtext.ScrolledText(preview_window, width=80, height=20)
                preview_text.pack(padx=10, pady=10)
                preview_text.insert(tk.END, df.to_string())
        except pymysql.MySQLError as db_err:
            logging.error(f"预览查询失败: {db_err}", exc_info=True)
            messagebox.showerror("错误", f"预览查询失败: {db_err}")
        except Exception as e:
            logging.error(f"预览查询失败: {e}", exc_info=True)
            messagebox.showerror("错误", f"预览查询失败: {e}")
        finally:
            conn.close()

    def test_connect_to_database(self):
        logging.debug("开始测试连接")
        conn = self.connect_to_database()
        if conn:
            messagebox.showinfo("成功", "数据库连接成功")
            conn.close()
        else:
            messagebox.showerror("错误", "数据库连接失败")

if __name__ == "__main__":
    root = tk.Tk()
    app = DatabaseQueryTool(root)
    root.mainloop()
